package cn.edu.hcnu.servlet;

import cn.edu.hcnu.entity.EmpEntity;

import javax.servlet.ServletException;
import javax.servlet.http.*;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;


public class EmpServlet extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

    }
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        String uri=request.getRequestURI();
        System.out.println(uri);
        uri=uri.substring(1);

        if("emp".equals(uri)){
            emp(request,response);

        }else  if("addEmp".equals(uri)){
            addEmp(request,response);

        }else  if("deleteEmp".equals(uri)){
            deleteEmp(request,response);
        }else  if("updateEmp".equals(uri)){
            updateEmp(request,response);
        }else  if("modifyEmp".equals(uri)){
            modifyEmp(request,response);
        }
    }

    public void addEmp(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String empno=request.getParameter("empno");
        String empname=request.getParameter("empname");
        String hiredate=request.getParameter("hiredate");

//        System.out.println(empno);
//        System.out.println(empname);
//        System.out.println(hiredate);

        String url="jdbc:oracle:thin:@localhost:1521:orcl";
        String username="scott";
        String password="tiger";


        Connection conn=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        String sql="INSERT  INTO emp(empno,ename,hiredate) VALUES (?,?,?)";

        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn= DriverManager.getConnection(url,username,password);

            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,Integer.parseInt(empno));
            pstmt.setString(2,empname);
            pstmt.setDate(3,java.sql.Date.valueOf(hiredate));
            pstmt.executeUpdate();
            conn.commit();
            request.getRequestDispatcher("emp").forward(request,response);


        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        } catch (ServletException e) {
            e.printStackTrace();
        } finally {
            try {
                pstmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }



    public void emp(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String url="jdbc:oracle:thin:@localhost:1521:orcl";
        String username="scott";
        String password="tiger";


        Connection conn=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        String sql="SELECT * FROM  emp";
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn= DriverManager.getConnection(url,username,password);
            pstmt=conn.prepareStatement(sql);
            rs=pstmt.executeQuery();
            List emplist=new ArrayList();
            while (rs.next()){
                EmpEntity emp=new EmpEntity();
                emp.setEmpno(rs.getInt("EMPNO"));
                emp.setEname(rs.getString("ENAME"));
                emp.setJob(rs.getString("JOB"));
                emp.setMgr(rs.getInt("MGR"));
                emp.setHiredate(rs.getDate("HIREDATE"));
                emp.setSal(rs.getFloat("SAL"));
                emp.setComm(rs.getFloat("COMM"));
                emp.setDeptno(rs.getInt("DEPTNO"));
                emplist.add(emp);
            }
            request.setAttribute("empData",emplist);
//            request.getRequestDispatcher("viewEmp.jsp").forward(request,response);
            request.getRequestDispatcher("index.jsp").forward(request,response);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }
    }


    public void deleteEmp(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String empno=request.getParameter("empno");
        System.out.println("empno+"+empno);

        String url="jdbc:oracle:thin:@localhost:1521:orcl";
        String username="scott";
        String password="tiger";


        Connection conn=null;
        PreparedStatement pstmt=null;
        String sql="delete from emp where empno=?";
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn= DriverManager.getConnection(url,username,password);
            conn.setAutoCommit(false);
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,Integer.parseInt(empno));
            pstmt.executeUpdate();
            conn.commit();

//            request.getRequestDispatcher("emp").forward(request,response);
            request.getRequestDispatcher("index.jsp").forward(request,response);

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }
    }



    public void updateEmp(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        String empno=request.getParameter("empno");
        System.out.println(empno);
        String url="jdbc:oracle:thin:@localhost:1521:orcl";
        String username="scott";
        String password="tiger";

        Connection conn=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        String sql="SELECT * FROM emp WHERE empno=?";
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn= DriverManager.getConnection(url,username,password);
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,Integer.parseInt(empno));
            rs=pstmt.executeQuery();

            EmpEntity emp=new EmpEntity();
            if(rs.next()){

                emp.setEmpno(rs.getInt("EMPNO"));
                emp.setEname(rs.getString("ENAME"));
                emp.setJob(rs.getString("JOB"));
                emp.setMgr(rs.getInt("MGR"));
                emp.setHiredate(rs.getDate("HIREDATE"));
                emp.setSal(rs.getFloat("SAL"));
                emp.setComm(rs.getFloat("COMM"));
                emp.setDeptno(rs.getInt("DEPTNO"));

            }
            request.setAttribute("emp",emp);
            request.getRequestDispatcher("updateEmp.jsp").forward(request,response);


        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            try {
                pstmt.close();
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
    public void modifyEmp(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        String empno=request.getParameter("empno");
        String empname=request.getParameter("empname");
        String hiredate=request.getParameter("hiredate");

        String url="jdbc:oracle:thin:@localhost:1521:orcl";
        String username="scott";
        String password="tiger";

        Connection conn=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        String sql="UPDATE emp SET ename=?,hiredate=? WHERE  empno=?";

        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn= DriverManager.getConnection(url,username,password);

            pstmt=conn.prepareStatement(sql);

            pstmt.setString(1,empname);
            pstmt.setDate(2,java.sql.Date.valueOf(hiredate));
            pstmt.setInt(3,Integer.parseInt(empno));
            pstmt.executeUpdate();
            conn.commit();
            request.getRequestDispatcher("emp").forward(request,response);


        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            try {
                pstmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}
